package com.mbc.rmt.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mbc.common.dao.DAO;
import com.mbc.rmt.data.AgentCOADataList;
import com.mbc.rmt.entities.AgentCOA;
import com.mbc.rmt.entities.AgentCOARpt;

public class AgentCOADAOImpls extends DAO<AgentCOA> implements AgentCOADAO {

	public AgentCOADAOImpls(Connection conn) {
		this.conn = conn;
		this.table_name = "AgentCOA";
	}

	@Override
	public void create(AgentCOA entity) throws SQLException {
		query = "insert into "
				+ table_name
				+ " ( agent_coa_id, agent_coa_code, description, accpayable, unrealize_ex_gl, write_off, accpayable_dept, unrealize_ex_gl_dept, write_off_dept, BranchId) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getAgent_coa_id());
		pstmt.setString(i++, entity.getAgent_coa_code());
		pstmt.setString(i++, entity.getDescription());
		pstmt.setLong(i++, entity.getAccpayable());
		pstmt.setLong(i++, entity.getUnrealize_ex_gl());
		pstmt.setLong(i++, entity.getWrite_off());
		pstmt.setLong(i++, entity.getAccpayable_dept());
		pstmt.setLong(i++, entity.getUnrealize_ex_gl_dept());
		pstmt.setLong(i++, entity.getWrite_off_dept());
		pstmt.setLong(i++, entity.getBranchId());
		pstmt.executeUpdate();
		pstmt.close();

	}

	@Override
	public void update(AgentCOA entity) throws SQLException {
		query = "update "
				+ table_name
				+ " set agent_coa_code = ?, description = ?, accpayable = ?, unrealize_ex_gl = ?, write_off = ?, accpayable_dept = ?, unrealize_ex_gl_dept = ?, write_off_dept = ?, BranchId = ?"
				+ "where agent_coa_id = " + entity.getAgent_coa_id();
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setString(i++, entity.getAgent_coa_code());
		pstmt.setString(i++, entity.getDescription());
		pstmt.setLong(i++, entity.getAccpayable());
		pstmt.setLong(i++, entity.getUnrealize_ex_gl());
		pstmt.setLong(i++, entity.getWrite_off());
		pstmt.setLong(i++, entity.getAccpayable_dept());
		pstmt.setLong(i++, entity.getUnrealize_ex_gl_dept());
		pstmt.setLong(i++, entity.getWrite_off_dept());
		pstmt.setLong(i++, entity.getBranchId());
		pstmt.executeUpdate();
		pstmt.close();

	}

	@Override
	public void delete(long key) throws SQLException {
		query = getDeleteQuery() + " where agent_coa_id = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();

	}

	@Override
	public AgentCOA find(long key) throws SQLException {
		AgentCOA l_AgentCOA = null;
		// query = getSelectQuery() + " where agent_coa_id = " + key;
		query = " select "
				+ "acoa.agent_coa_id,acoa.agent_coa_code,acoa.description, "
				+ "acoa.accpayable,acrcoa.AC_ID accpayable_code, acoa.accpayable_dept,acrdep.departmentCode accpayable_dept_code, "
				+ "acoa.unrealize_ex_gl,urcoa.AC_ID unrealize_ex_gl_code,acoa.unrealize_ex_gl_dept,urdep.departmentCode unrealize_ex_gl_dept_code, "
				+ "acoa.write_off,wcoa.AC_ID write_off_code,acoa.write_off_dept,wdep.departmentCode write_off_dept_code "
				+ "from dbo.AgentCOA acoa "
				+ "LEFT JOIN dbo.COA acrcoa ON acoa.accpayable=acrcoa.COAKey "
				+ "LEFT JOIN dbo.Department acrdep ON acoa.accpayable_dept=acrdep.departmentID "
				+ "LEFT JOIN dbo.COA urcoa ON acoa.unrealize_ex_gl=urcoa.COAKey "
				+ "LEFT JOIN dbo.Department urdep ON acoa.unrealize_ex_gl_dept=urdep.departmentID "
				+ "LEFT JOIN dbo.COA wcoa ON acoa.write_off=wcoa.COAKey "
				+ "LEFT JOIN dbo.Department wdep ON acoa.write_off_dept=wdep.departmentID "
				+ " where agent_coa_id = " + key;
		
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_AgentCOA = readRecord1(rs);
		rs.close();
		pstmt.close();
		return l_AgentCOA;
	}

	@Override
	public AgentCOA findByCode(String code,long branchId) throws SQLException {
		AgentCOA l_AgentCOA = null;
		query = " select row_number() over(order by agent_coa_id desc)as [rowno], "
				+ "acoa.agent_coa_id,acoa.agent_coa_code,acoa.description, acoa.BranchId, "
				+ "acoa.accpayable,acrcoa.AC_ID accpayable_code, acoa.accpayable_dept,acrdep.departmentCode accpayable_dept_code, "
				+ "acoa.unrealize_ex_gl,urcoa.AC_ID unrealize_ex_gl_code,acoa.unrealize_ex_gl_dept,urdep.departmentCode unrealize_ex_gl_dept_code, "
				+ "acoa.write_off,wcoa.AC_ID write_off_code,acoa.write_off_dept,wdep.departmentCode write_off_dept_code "
				+ "from dbo.AgentCOA acoa "
				+ "LEFT JOIN dbo.COA acrcoa ON acoa.accpayable=acrcoa.COAKey "
				+ "LEFT JOIN dbo.Department acrdep ON acoa.accpayable_dept=acrdep.departmentID "
				+ "LEFT JOIN dbo.COA urcoa ON acoa.unrealize_ex_gl=urcoa.COAKey "
				+ "LEFT JOIN dbo.Department urdep ON acoa.unrealize_ex_gl_dept=urdep.departmentID "
				+ "LEFT JOIN dbo.COA wcoa ON acoa.write_off=wcoa.COAKey "
				+ "LEFT JOIN dbo.Department wdep ON acoa.write_off_dept=wdep.departmentID "
				+ " where agent_coa_code = '" + code 
				+ "' and acoa.BranchId = " + branchId;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_AgentCOA = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_AgentCOA;
	}

	@Override
	public ArrayList<AgentCOA> findAll() throws SQLException {
		ArrayList<AgentCOA> l_AgentCOAList = new ArrayList<AgentCOA>();
		query = getSelectQuery();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_AgentCOAList.add(readRecord(rs));
		}
		rs.close();
		pstmt.close();
		return l_AgentCOAList;
	}

	@Override
	public AgentCOADataList findAgentCOAs(AgentCOA cri)
			throws SQLException {
		AgentCOADataList l_DataList = new AgentCOADataList();
		ArrayList<AgentCOA> l_entityList = new ArrayList<AgentCOA>();
		/*String l_cri = "and (agent_coa_code like N'%" + cri.getKeyword() + "%'"
				+ " or acoa.description like N'%" + cri.getKeyword() + "%')"
				+ " and acoa.BranchId = " + cri.getBranchId();*/
		String l_cri = " and (" + cri.getFieldname() + " like N'%" + cri.getKeyword() + "%') and acoa.BranchId = " + cri.getBranchId();

		query = "set rowcount "
				+ cri.getSize()
				+ " select * from (select row_number() over(order by agent_coa_id desc)as [rowno], "
				+ "acoa.agent_coa_id,acoa.agent_coa_code,acoa.description, acoa.BranchId, "
				+ "acoa.accpayable,acrcoa.AC_ID accpayable_code, acoa.accpayable_dept,acrdep.departmentCode accpayable_dept_code, "
				+ "acoa.unrealize_ex_gl,urcoa.AC_ID unrealize_ex_gl_code,acoa.unrealize_ex_gl_dept,urdep.departmentCode unrealize_ex_gl_dept_code, "
				+ "acoa.write_off,wcoa.AC_ID write_off_code,acoa.write_off_dept,wdep.departmentCode write_off_dept_code "
				+ "from dbo.AgentCOA acoa "
				+ "LEFT JOIN dbo.COA acrcoa ON acoa.accpayable=acrcoa.COAKey "
				+ "LEFT JOIN dbo.Department acrdep ON acoa.accpayable_dept=acrdep.departmentID "
				+ "LEFT JOIN dbo.COA urcoa ON acoa.unrealize_ex_gl=urcoa.COAKey "
				+ "LEFT JOIN dbo.Department urdep ON acoa.unrealize_ex_gl_dept=urdep.departmentID "
				+ "LEFT JOIN dbo.COA wcoa ON acoa.write_off=wcoa.COAKey "
				+ "LEFT JOIN dbo.Department wdep ON acoa.write_off_dept=wdep.departmentID "
				+ "where 1 = 1" + l_cri + ") " + table_name + " where rowno > "
				+ cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			do {
				l_entityList.add(readRecord(rs));
			} while (rs.next());
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		String l_cri1 = "and (agent_coa_code like N'%" + cri.getKeyword() + "%'"
				+ " or description like N'%" + cri.getKeyword() + "%')"
				+ " and BranchId = " + cri.getBranchId();
		query = "select count(agent_coa_id) from " + table_name
				+ " where 1 = 1" + l_cri1;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}
	
	protected AgentCOA readRecord1(ResultSet arg0) throws SQLException {
		AgentCOA l_entity = new AgentCOA();
		l_entity.setAgent_coa_id(rs.getLong("agent_coa_id"));
		l_entity.setAgent_coa_code(rs.getString("agent_coa_code"));
		l_entity.setDescription(rs.getString("description"));

		l_entity.setAccpayable(rs.getLong("accpayable"));
		l_entity.setAccpayable_code(rs.getString("accpayable_code"));

		l_entity.setUnrealize_ex_gl(rs.getLong("unrealize_ex_gl"));
		l_entity.setUnrealize_ex_gl_code(rs.getString("unrealize_ex_gl_code"));

		l_entity.setWrite_off(rs.getLong("write_off"));
		l_entity.setWrite_off_code(rs.getString("write_off_code"));

		l_entity.setAccpayable_dept(rs.getLong("accpayable_dept"));
		l_entity.setAccpayable_dept_code(rs.getString("accpayable_dept_code"));

		l_entity.setUnrealize_ex_gl_dept(rs.getLong("unrealize_ex_gl_dept"));
		l_entity.setUnrealize_ex_gl_dept_code(rs
				.getString("unrealize_ex_gl_dept_code"));

		l_entity.setWrite_off_dept(rs.getLong("write_off_dept"));
		l_entity.setWrite_off_dept_code(rs.getString("write_off_dept_code"));

		return l_entity;
	}

	@Override
	protected AgentCOA readRecord(ResultSet arg0) throws SQLException {
		AgentCOA l_entity = new AgentCOA();
		l_entity.setAgent_coa_id(rs.getLong("agent_coa_id"));
		l_entity.setAgent_coa_code(rs.getString("agent_coa_code"));
		l_entity.setDescription(rs.getString("description"));

		l_entity.setAccpayable(rs.getLong("accpayable"));
		l_entity.setAccpayable_code(rs.getString("accpayable_code"));

		l_entity.setUnrealize_ex_gl(rs.getLong("unrealize_ex_gl"));
		l_entity.setUnrealize_ex_gl_code(rs.getString("unrealize_ex_gl_code"));

		l_entity.setWrite_off(rs.getLong("write_off"));
		l_entity.setWrite_off_code(rs.getString("write_off_code"));

		l_entity.setAccpayable_dept(rs.getLong("accpayable_dept"));
		l_entity.setAccpayable_dept_code(rs.getString("accpayable_dept_code"));

		l_entity.setUnrealize_ex_gl_dept(rs.getLong("unrealize_ex_gl_dept"));
		l_entity.setUnrealize_ex_gl_dept_code(rs
				.getString("unrealize_ex_gl_dept_code"));

		l_entity.setWrite_off_dept(rs.getLong("write_off_dept"));
		l_entity.setWrite_off_dept_code(rs.getString("write_off_dept_code"));

		l_entity.setBranchId(rs.getLong("BranchId"));
		return l_entity;
	}

	public ResultSet prepareAgentCOAReport(AgentCOARpt info)
			throws SQLException {
		ResultSet l_rs;
		String l_query = "dbo.sp_cr_AgentCOARpt ?, ?, ?";

		CallableStatement l_pstmt = conn.prepareCall(l_query);
		AgentCOARptParameter(l_pstmt, info);
		l_rs = l_pstmt.executeQuery();
		return l_rs;

	}

	private void AgentCOARptParameter(CallableStatement l_pstmt,
			AgentCOARpt info) throws SQLException {
		int i = 1;

		l_pstmt.setString(i++, info.getFromAgentCOAID());
		l_pstmt.setString(i++, info.getToAgentCOAID());
		l_pstmt.setLong(i++, info.getBranchId());
	}

	@Override
	public AgentCOADataList getAgentCOAList(AgentCOARpt info)
			throws SQLException {
		AgentCOADataList l_DataList= new AgentCOADataList();
		ArrayList<AgentCOA> l_AgentCOAList = new ArrayList<AgentCOA>();
		String l_query = "dbo.sp_cr_AgentCOARpt ?, ?, ?";
		CallableStatement l_pstmt = conn.prepareCall(l_query);
		AgentCOARptParameter(l_pstmt, info);
		ResultSet l_rs = l_pstmt.executeQuery();
		while (l_rs.next()) {
			l_AgentCOAList.add(readRecordFT(l_rs));
		}
		l_DataList.setEntityList(l_AgentCOAList);
		l_rs.close();
		l_pstmt.close();
		return l_DataList;
	}

	private AgentCOA readRecordFT(ResultSet rs) throws SQLException {
		AgentCOA l_entity = new AgentCOA();

		l_entity.setAgent_coa_code(rs.getString("agent_coa_code"));
		l_entity.setDescription(rs.getString("description"));

		l_entity.setAccpayable_code(rs.getString("accpayable_code"));

		l_entity.setUnrealize_ex_gl_code(rs.getString("unrealize_ex_gl_code"));

		l_entity.setWrite_off_code(rs.getString("write_off_code"));

		l_entity.setAccpayable_dept_code(rs.getString("accpayable_dept_code"));

		l_entity.setUnrealize_ex_gl_dept_code(rs
				.getString("unrealize_ex_gl_dept_code"));

		l_entity.setWrite_off_dept_code(rs.getString("write_off_dept_code"));

		return l_entity;
	}

}
